import time

import requests

import dao


def jsonToDb():
    dao.saveSql("truncate table t_nsc;")
    sql = "insert into t_nsc (ids,C_C_NAME,C_STD_CODE,STD_NATURE,ACT_DATE,STATE,ISSUE_DATE,PROJECT_ID) values ('%s','%s','%s','%s','%s','%s','%s','%s');"
    count = 1
    while count <= 1265:
        url = 'http://std.samr.gov.cn/gb/search/gbQueryPage' \
              '?searchText=&ics=&state=&ISSUE_DATE=&sortOrder=asc&pageSize=50&pageNumber=' + \
              str(count) + '&_=1649870881549'
        resJson = requests.get(url)
        json = resJson.json()
        rows = json['rows']
        for row in rows:
            # 判断是否存在ACT_DATE
            if row.__contains__("ACT_DATE"):
                ACT_DATE = row['ACT_DATE']
            else:
                ACT_DATE = "null"
            # 判断是否存在ISSUE_DATE
            if row.__contains__('ISSUE_DATE'):
                ISSUE_DATE = row['ISSUE_DATE']
            else:
                ISSUE_DATE = "null"
            try:
                realSql = sql % (
                    row['id'], row['C_C_NAME'], row['C_STD_CODE'], row['STD_NATURE'], ACT_DATE, row['STATE'],
                    ISSUE_DATE, row['PROJECT_ID'])
                dao.saveSql(realSql)
            except:
                print(row)
        print("插入数据到数据库：" + str(count))
        count += 1


def sqlSave():
    sql = "insert into t_nsc (ids,C_C_NAME,C_STD_CODE,STD_NATURE,ACT_DATE,STATE,ISSUE_DATE,PROJECT_ID) values ('%s','%s','%s','%s','%s','%s','%s','%s');"
    count = 1
    with open("sql/sqlSave2.txt", 'w', encoding='utf-8') as f:
        while count <= 1265:
            url = 'http://std.samr.gov.cn/gb/search/gbQueryPage' \
                  '?searchText=&ics=&state=&ISSUE_DATE=&sortOrder=asc&pageSize=50&pageNumber=' + \
                  str(count) + '&_=1649870881549'
            resJson = requests.get(url)
            json = resJson.json()
            try:
                rows = json['rows']
            except:
                print(rows)
            for row in rows:
                # 判断是否存在ACT_DATE
                if row.__contains__("ACT_DATE"):
                    ACT_DATE = row['ACT_DATE']
                else:
                    ACT_DATE = "null"
                # 判断是否存在ISSUE_DATE
                if row.__contains__('ISSUE_DATE'):
                    ISSUE_DATE = row['ISSUE_DATE']
                else:
                    ISSUE_DATE = "null"
                try:
                    realSql = sql % (
                        row['id'], row['C_C_NAME'], row['C_STD_CODE'], row['STD_NATURE'], ACT_DATE, row['STATE'],
                        ISSUE_DATE, row['PROJECT_ID'])
                    f.write(realSql + '\n')
                except:
                    print(row)
            print("保存插入语句：" + str(count))
            count += 1


def exeSqlByFile():
    f = open("sql/sqlSave2.txt", 'r', encoding='utf-8')
    i = 1
    for sql in f:
        print(str(round(i * 100 / 63250, 2)) + "%:" + sql)
        dao.saveSql(sql)
        # time.sleep(1)
        i += 1
    f.close()


if __name__ == '__main__':
    # sqlSave()
    # jsonToDb()
    dao.saveSql("truncate table t_nsc;")
    exeSqlByFile()
